from exts import db
from datetime import datetime
from sqlalchemy import DDL, event


# 定义ORM模型
class AdminModel(db.Model):
    __tablename__ = "admin"
    name = db.Column(db.String(20), nullable=False, primary_key=True)
    password = db.Column(db.String(20), nullable=False)
    email = db.Column(db.String(20))
    type = db.Column(db.String(20), nullable=False,default="admin")


class BorrowerModel(db.Model):
    __tablename__ = "borrower"
    name = db.Column(db.String(20), nullable=False, primary_key=True)
    password = db.Column(db.String(20), nullable=False)
    email = db.Column(db.String(20))
    phone=db.Column(db.String(20))
    type = db.Column(db.String(20), nullable=False, default="student")

class Book_styleModel(db.Model):
    __tablename__ = "book_style"
    type_id = db.Column(db.Integer, autoincrement=True, nullable=False)
    type_name = db.Column(db.String(20), primary_key=True, nullable=False)

class BookModel(db.Model):
    __tablename__ = "book"
    book_id = db.Column(db.String(20), primary_key=True, nullable=False)
    book_name = db.Column(db.String(20), nullable=False)
    book_author = db.Column(db.String(20), nullable=False)
    book_type = db.Column(db.String(20), db.ForeignKey('book_style.type_name'), nullable=False)
    book_publish = db.Column(db.String(20), nullable=False)
    book_publish_time = db.Column(db.Integer)
    book_price = db.Column(db.Integer)
    book_input_time = db.Column(db.Date, default=datetime.now().date())
    book_amount = db.Column(db.Integer)
    book_stock = db.Column(db.Integer)

    # borrower = db.relationship("Borrower", backref="borrowed_book")


class Book_statusModel(db.Model):
    # __table__ = None
    __tablename__ = "book_status"
    book_id = db.Column(db.String(20), db.ForeignKey('book.book_id'), primary_key=True, nullable=False)
    book_name = db.Column(db.String(20), nullable=False)
    borrower_name = db.Column(db.String(20), db.ForeignKey('borrower.name'), primary_key=True, nullable=False)
    borrow_time = db.Column(db.Date, default=datetime.now().date())
    book_state = db.Column(db.String(20))





# # 创建触发器
# # 还一本书，book表中对应书籍库存+1
# status_return = DDL('''
# CREATE DEFINER=`root`@`localhost` TRIGGER `book_return` AFTER DELETE ON `book_status` FOR EACH ROW BEGIN UPDATE book SET book_stock=book_stock+1 WHERE book.book_id=book_id;END;
# ''')
# # 借一本书，book表中对应书籍库存-1
# status_borrow = DDL('''
# CREATE DEFINER=`root`@`localhost` TRIGGER `book_borrow` AFTER INSERT ON `book_status` FOR EACH ROW BEGIN UPDATE book SET book_stock=book_stock-1 WHERE book.book_id=book_id; END;
# ''')
#
# event.listen(Book_status.__table__, 'after_create', status_return)
# event.listen(Book_status.__table__, 'after_create', status_borrow)

'''

    #条件查询  
    #这种书有多少人借，通过书ID
    #这个人借了什么书，通过借阅者ID
一、管理员
1、图书管理
    入库
    删除
    更改
    查询
        模糊查询
2、借阅者管理
    查询
        模糊查询
    更改
    删除

二、未登录用户
    图书查询

三、已登录用户
    图书查询
    图书借阅
    自己借阅信息：借阅者ID

'''
